#!/usr/bin/python
# -*- coding: utf-8 -*-

import xml.etree.ElementTree as ET
from string import *
import os, sys, time

#sys.setdefaultencoding('UTF_8')

def xpath_paser_xml(fp):
    ExportFilePath = os.environ['APPHOME']+'/sql/table/'
    a = "attribute" ; c = "collection" ; o = "object"
    doc = ET.parse(fp).getroot().findall('.//{%s}Package' % (o))
    print '-- parse over --' + time.strftime('%X', time.localtime())
    
    for oPackage in doc:
        aCode = oPackage.find('.//{%s}Code' % a)
        if aCode is None:
            continue

        fileNewName = ExportFilePath + (aCode.text).lower() + '.txt'
        if os.path.exists(fileNewName):
            os.remove(fileNewName)
        fp_temp = open(fileNewName, 'a+')
        fp_temp.write("-- 此文档由工具自行生成，请勿动 --author: Mr.wangpeng\n" )
    
        oTables = oPackage.findall('.//{%s}Table' % (o))
        for table in oTables:
            aTableCode = table.findtext('{%s}Code' % a)
            aTableName = table.findtext('{%s}Name' % a)
            aTableComment = table.findtext('{%s}Comment' % a)
            if aTableCode is not None:
                fp_temp.write('-- ' + aTableCode)
            else:
                continue
            if aTableName is not None:
                fp_temp.write('-- ' + aTableName + '\n')
            if aTableComment is not None:
                aTableComment = replace(aTableComment,'\n','  ')
                fp_temp.write('-- ' + aTableComment + '\n') 
    
            oColKeys = table.findall('.//{%s}Keys/{%s}Key' % (c,o))
            strColKeyCode = ''; strColKeyCode = []; lstColKeysId = []
            for oColKey in oColKeys:
                strColKeyCode = oColKey.findtext('{%s}Code' % a)
                strColKeyCode = 'P_' + strColKeyCode.upper()
                oKeyColumns = oColKey.findall('.//{%s}Key.Columns/{%s}Column' % (c,o))
                lstColKeysRef = [ oColKey.get('Ref') for oColKey in oKeyColumns if len(oColKey.attrib) > 0 ]

            oColumn = table.findall('.//{%s}Columns/{%s}Column' % (c,o))
            fp_temp.write('CONNECT TO %s' % os.environ['DBNAME'] + ';\n' +
                          'DROP   TABLE  ' + aTableCode + ';\n' +
                          'CREATE TABLE  ' + aTableCode + ' (\n'
                          )
            for Columns in oColumn:
                aColCode = Columns.findtext('{%s}Code' % a)
                aColName = Columns.findtext('{%s}Name' % a)
                aColDataType = Columns.findtext('{%s}DataType' % a)
                aColMandatory = Columns.findtext('{%s}Mandatory' % a)
                aColComment = Columns.findtext('{%s}Comment' % a)
                strColMandatory = ' '*7 + ','
                if aColMandatory and int(aColMandatory) == 1:
                    strColMandatory = 'NOT NULL,'
                if aColComment is None:
                    aColComment = ' '
                else:
                    aColComment = replace(aColComment, '\n', ' ')
                    aColComment = replace(aColComment, '    ', '  ')
                    if len(aColComment) > 40:
                        aColComment = '\n' + ' '*8 + '--' + aColComment
    
                if lstColKeysRef.count(Columns.get('Id')) > 0:
                    lstColKeysId.append(aColCode)
    
                fp_temp.write(' '*4 + aColCode + 
                              ' '*(30 - len(aColCode))     + aColDataType    +
                              ' '*(15 - len(aColDataType)) + strColMandatory +
                              '--' + aColName + 
                              ':' + aColComment +'\n'
                              )
            if len(strColKeyCode) > 0:
                str = (' '*4 + 'constraint  \'%s\'' + '  primary key(') % strColKeyCode
                fp_temp.write(str)
                str = ''
                for i in lstColKeysId:
                    str = str + i + ','
                fp_temp.write(str[:-1] + ')\n')
    
            fp_temp.write(');\n' + 
                          'COMMIT WORK;'    + '\n' +
                          'CONNECT RESET;'  + '\n' +
                          'TERMINATE;'      + '\n' +
                          '-' * 30 + '\n\n'
                          )
        fp_temp.close()


if __name__ == '__main__':
    print '-- Starting --' + time.strftime('%X', time.localtime())
    if len(sys.argv) < 2:
        fp_name = 'topcard20081209_2.xml'
    else:
        fp_name = sys.argv[1]
    try:
        fp = open(fp_name, "r")
    except:
        print "open xml failed"
        sys.exit()

    xpath_paser_xml(fp)
    fp.close()
    print '-- end --' + time.strftime('%X', time.localtime())

